package dao;

import pojo.DefectiveProduct;
import utils.JDBCUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DefectiveProductDAOImpl {

    private static final String TABLE_NAME = "Defective_Product";

    public static void add(DefectiveProduct defectiveProduct) throws SQLException {
        String sql = "INSERT INTO " + TABLE_NAME + " (scrap_number, item_number, reasons) VALUES (?, ?, ?)";
        JDBCUtils.execute(sql, null, defectiveProduct.getItemNumber(), defectiveProduct.getReasons());
    }

    public static void delete(int scrapNumber) throws SQLException {
        String sql = "DELETE FROM " + TABLE_NAME + " WHERE scrap_number = ?";
        JDBCUtils.execute(sql, scrapNumber);
    }

    public static void update(DefectiveProduct defectiveProduct) throws SQLException {
        String sql = "UPDATE " + TABLE_NAME + " SET item_number = ?, reasons = ? WHERE scrap_number = ?";
        JDBCUtils.execute(sql, defectiveProduct.getItemNumber(), defectiveProduct.getReasons(), defectiveProduct.getScrapNumber());
    }

    public static DefectiveProduct getById(int scrapNumber) throws SQLException {
        String sql = "SELECT * FROM " + TABLE_NAME + " WHERE scrap_number = ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, scrapNumber);
        if (resultSet.next()) {
            return createDefectiveProductFromResultSet(resultSet);
        }
        return null;
    }

    public static List<DefectiveProduct> getAll() throws SQLException {
        List<DefectiveProduct> defectiveProducts = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME;
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql);
        while (resultSet.next()) {
            DefectiveProduct defectiveProduct = createDefectiveProductFromResultSet(resultSet);
            defectiveProducts.add(defectiveProduct);
        }
        return defectiveProducts;
    }

    public static int getCount() throws SQLException {
        String sql = "SELECT COUNT(*) AS count FROM " + TABLE_NAME;
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql);
        if (resultSet.next()) {
            return resultSet.getInt("count");
        }
        return 0;
    }

    public static List<DefectiveProduct> getByPage(int start, int pageSize) throws SQLException {
        List<DefectiveProduct> defectiveProducts = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME + " LIMIT ?, ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, start, pageSize);
        while (resultSet.next()) {
            DefectiveProduct defectiveProduct = createDefectiveProductFromResultSet(resultSet);
            defectiveProducts.add(defectiveProduct);
        }
        return defectiveProducts;
    }

    public static List<DefectiveProduct> findByKeyword(String keyword) throws SQLException {
        List<DefectiveProduct> defectiveProducts = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME + " WHERE reasons LIKE ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, "%" + keyword + "%");
        while (resultSet.next()) {
            DefectiveProduct defectiveProduct = createDefectiveProductFromResultSet(resultSet);
            defectiveProducts.add(defectiveProduct);
        }
        return defectiveProducts;
    }

    public static List<DefectiveProduct> findByPageAndKeyword(int start, int pageSize, String keyword) throws SQLException {
        List<DefectiveProduct> defectiveProducts = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME + " WHERE reasons LIKE ? LIMIT ?, ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, "%" + keyword + "%", start, pageSize);
        while (resultSet.next()) {
            DefectiveProduct defectiveProduct = createDefectiveProductFromResultSet(resultSet);
            defectiveProducts.add(defectiveProduct);
        }
        return defectiveProducts;
    }

    private static DefectiveProduct createDefectiveProductFromResultSet(ResultSet resultSet) throws SQLException {
        int scrapNumber = resultSet.getInt("scrap_number");
        int itemNumber = resultSet.getInt("item_number");
        String reasons = resultSet.getString("reasons");
        return new DefectiveProduct(scrapNumber, itemNumber, reasons);
    }
}
